﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
   public class 账单查询DAL
    {
       //查询所有的付款记录人
       public static DataSet FuKuanRen() {
           string sql = "select UserID,UserName from admins";
           return DBHelper.Duankaishi(sql,null);
       }
       public static SqlDataReader query()
       {

           string sql = @"select CBStartDate,c.CBID,TableName,CBEndDate,CBSale,CDSale,(CDMoney-CDSale),VipName from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID 
            left join dbo.Vips v on c. VipID=v.VipID ";
           return DBHelper.Zhilianshi(sql,null); ;
       }
       //查询所有的消费明细信息
       public static SqlDataReader XiaoFei()
       {

           string sql = " select *  from ConsumerDetail";
           return DBHelper.Zhilianshi(sql, null);
       }
       //时间查询
       public static SqlDataReader ShijianChaXun(Model_ConsumerBill_Tables_ConsumerDetail_Vips m,string a) {
           string sql = @"select CBStartDate,c.CBID,TableName,CBEndDate,CBSale,CDSale,(CDMoney-CDSale),VipName from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID 
            left join dbo.Vips v on c. VipID=v.VipID where CBStartDate >= @CBStartDate and CBEndDate <=@CBEndDate";
           if (a == null)
           {
                sql = @"select CBStartDate,c.CBID,TableName,CBEndDate,CBSale,CDSale,(CDMoney-CDSale),VipName from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID 
            left join dbo.Vips v on c. VipID=v.VipID where CBStartDate >= @CBStartDate and CBEndDate < @CBEndDate";
           }
           List<SqlParameter> list = new List<SqlParameter>()
           {
             new SqlParameter("@CBStartDate",DateTime.Parse(m.CBStartDate)),
            new SqlParameter("@CBEndDate",DateTime.Parse(m.CBEndDate))

           };
           return DBHelper.Zhilianshi(sql,list.ToArray());
       }
       public static SqlDataReader query(Model_ConsumerBill_Tables_ConsumerDetail_Vips m)
       {

           string sql = @"select ProductName,CDPrice,CDAmount,CDMoney,CDSale,(CDMoney-CDSale),CDDate from dbo.Products p
inner join dbo.ConsumerDetail c on c.ProdcutID=p.ProductID where c.CBID=@CBID";
           List<SqlParameter> list = new List<SqlParameter>()
           {
               new SqlParameter("@CBID",m.CBID)
           };
           return DBHelper.Zhilianshi(sql, list.ToArray()); ;
       }
       //条件查询
       public static SqlDataReader Tiaojianquery(Model_ConsumerBill_Tables_ConsumerDetail_Vips m)
       {
           string sql = @"select CBStartDate,c.CBID,TableName,CBEndDate,CBSale,CDSale,(CDMoney-CDSale),CDMoney,VipName from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID 
            left join dbo.Vips v on c. VipID=v.VipID  inner join admins ad on c.AdminID =ad.UserID where AdminID = @AdminID";
           List<SqlParameter> list = new List<SqlParameter>();
           list.Add(new SqlParameter("@AdminID", Admins.UserID));
           if (m.TableID !="")
           {
               sql += " and c.TableID=@TableID";
               list.Add(new SqlParameter("@TableID", m.TableID));
           }
           return DBHelper.Zhilianshi(sql, list.ToArray()); ;
       }
    }
}
